# -*- coding: utf-8 -*-
# @Time : 2022/5/5 14:38
# @Author : x30003433


# 参考：https://www.runoob.com/python3/python-mysql-connector.html

# pip install mysql-connector
import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",  # 数据库主机地址
    user="root",  # 数据库用户名
    passwd="root",  # 数据库密码
    database="xqx",
    buffered=True
)
mycursor = mydb.cursor()
# # 创建数据库
# mycursor.execute("CREATE DATABASE If Not Exists xqx")
#
# # 列出所有的数据库
# mycursor.execute("SHOW DATABASES")
# for x in mycursor:
#     print(x)
#
# # 切换对应的数据库
# mycursor.execute("USE xqx")

# 列出所有的数据库
mycursor.execute("SHOW TABLES")
print(mycursor.fetchall())


def create_table():
    # 创建表
    create_table_sql = '''
    CREATE TABLE IF NOT EXISTS foods (
        food_name VARCHAR(100), 
        food_type INT,
        quantity INT,
        weight INT,
        create_date DATE, 
        save_time INT,
        PRIMARY KEY (food_name)
    )
    '''
    mycursor.execute(create_table_sql)
    mydb.commit()


def insert_data():
    # 插入数据
    insert_data_sql = '''
    INSERT INTO foods
    (food_name,food_type,quantity,weight,create_date,save_time)
    VALUES
    ('cucumber',0,3,NULL,'2022-05-05',7)
    
    '''
    mycursor.execute(insert_data_sql)
    mydb.commit()
    print(mycursor.rowcount, "记录插入成功。")


def insert_total_data():
    # 插入数据
    sql = "INSERT INTO foods (food_name,food_type,quantity,weight,create_date,save_time) VALUES (%s, %s, %s, %s, %s, %s)"
    val = [
        ('cucumber', 0, 1, None, '2022-05-05', 7),
        ('cucumber1', 0, 2, None, '2022-05-05', 7),
        ('Tomatoes', 0, None, 1, '2022-05-05', 7),
        ('Tomatoes1', 0, None, 2, '2022-05-05', 7)
    ]
    try:
        mycursor.executemany(sql, val)
        mydb.commit()
        print(mycursor.rowcount, "记录插入成功。")
    except:
        # 发生错误时回滚
        mydb.rollback()


def update_data():
    # 更新数据
    update_data_sql = '''
    UPDATE foods
    SET quantity=5
    WHERE food_name='cucumber'
    '''
    mycursor.execute(update_data_sql)
    mydb.commit()
    print(mycursor.rowcount, " 条记录被修改")


def delete_data():
    # 删除数据
    delete_data_sql = '''
    DELETE FROM foods
    WHERE food_name='cucumber'
    '''
    mycursor.execute(delete_data_sql)
    mydb.commit()
    print(mycursor.rowcount, " 条记录删除")


def get_data():
    # 查询数据
    get_data_sql = '''
    SELECT * 
    FROM foods
    '''
    mycursor.execute(get_data_sql)
    myresult = mycursor.fetchall()  # fetchall() 获取所有记录

    for x in myresult:
        print(x)

    myresult = mycursor.fetchone()
    print(myresult)


insert_total_data()
# 关闭数据库连接
mydb.close()
